package utils.excel;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.FileInputStream;

/**
 * Created by lim on 16/9/1.
 */
public class PoiReadExcelUtils {

    private Workbook workbook = null;
    private boolean bHasReaded = false;
    private Sheet CurSheet = null;

    /**
     * 载入Excel数据
     * @param sFilePath
     * @return
     */
    public Workbook ReadExcel(String sFilePath)
    {
        bHasReaded = false;
        try{
            if(sFilePath.indexOf(".xlsx") > -1){
                workbook = new XSSFWorkbook(new FileInputStream(sFilePath));
                bHasReaded = true;
            }else {
                workbook = new HSSFWorkbook(new FileInputStream(sFilePath));
                bHasReaded = true;
            }
        }catch (Exception e){
            e.printStackTrace();
        }
        return workbook;
    }

    /**
     * 返回sheet个数
     * @return
     */
    public int getSheetNumber(){
        if(bHasReaded){
            return workbook.getNumberOfSheets();
        }else {
            return  -1;
        }
    }

    /**
     * 根据sheet名称获取sheet
     * @param sSheetName
     * @return
     */
    public Sheet getSheet(String sSheetName){
        if(bHasReaded){
            return workbook.getSheet(sSheetName);
        }else {
            return null;
        }
    }

    /**
     * 根据索引获取Sheet
     * @param iIndex
     * @return
     */
    public Sheet getSheet(int iIndex){
        if(bHasReaded){
            if(iIndex < getSheetNumber()){
                return workbook.getSheetAt(iIndex);
            }else {
                return null;
            }
        }else {
            return null;
        }
    }

    public Sheet getCurSheet() {
        return CurSheet;
    }

    public void setCurSheet(Sheet curSheet) {
        CurSheet = curSheet;
    }

    /**
     * 获取表格
     * @param iRow
     * @param iCol
     * @return
     */
    public Cell getCell(int iRow, int iCol){
        Cell cell = null;
        if(bHasReaded && CurSheet != null){
            if(iRow < CurSheet.getPhysicalNumberOfRows()){
                if(iCol < CurSheet.getRow(iRow).getLastCellNum()){
                    cell = CurSheet.getRow(iRow).getCell(iCol);
                }
            }
        }
        return cell;
    }

    /**
     * 表格数据
     * @param iRow
     * @param iCol
     * @return
     */
    public String getCellValue(int iRow, int iCol){
        Cell cell = getCell(iRow, iCol);
        String sReturn = "";
        if (cell != null){
            switch (cell.getCellType()) {
                case Cell.CELL_TYPE_NUMERIC:
                    if(DateUtil.isCellDateFormatted(cell)){
                        sReturn = cell.getDateCellValue().toString();
                    }
                    sReturn =  String.valueOf(cell.getNumericCellValue());
                    break;
                case Cell.CELL_TYPE_STRING:
                    sReturn =  cell.getStringCellValue();
                    break;
                case Cell.CELL_TYPE_FORMULA:
                    sReturn =  cell.getCellFormula();
                    break;
                case Cell.CELL_TYPE_BLANK:
                    sReturn =  "";
                    break;
                case Cell.CELL_TYPE_BOOLEAN:
                    sReturn =  cell.getBooleanCellValue() + "";
                    break;
                case Cell.CELL_TYPE_ERROR:
                    sReturn =  cell.getErrorCellValue() + "";
                    break;
            }
        }else {
            sReturn =  "";
        }
        return sReturn;
    }
}
